Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Cursor Attributes

Every explicit cursor and cursor variable has four attributes: `%FOUND`, `%ISOPEN` `%NOTFOUND`, and `%ROWCOUNT`. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement. For more information, see "Using Cursor Expressions".

The implicit cursor `SQL` has additional attributes, `%BULK_ROWCOUNT` and `%BULK_EXCEPTIONS`. For more information, see "SQL Cursor".

Syntax

cursor attribute ::=

Description of the illustration cursor_attribute.gif

Keyword and Parameter Description

cursor_name

An explicit cursor previously declared within the current scope.

cursor_variable_name

A PL/SQL cursor variable (or parameter) previously declared within the current scope.

%FOUND Attribute

A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, `cursor_name%FOUND` returns `NULL`. Afterward, it returns `TRUE` if the last fetch returned a row, or `FALSE` if the last fetch failed to return a row.

host_cursor_variable_name

A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

%ISOPEN Attribute

A cursor attribute that can be appended to the name of a cursor or cursor variable. If a cursor is open, `cursor_name%ISOPEN` returns `TRUE`; otherwise, it returns `FALSE`.

%NOTFOUND Attribute

A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, `cursor_name%NOTFOUND` returns `NULL`. Thereafter, it returns `FALSE` if the last fetch returned a row, or `TRUE` if the last fetch failed to return a row.

%ROWCOUNT Attribute

A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, `%ROWCOUNT` is zeroed. Before the first fetch, `cursor_name%ROWCOUNT` returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

Usage Notes

The cursor attributes apply to every cursor or cursor variable. For example, you can open multiple cursors, then use `%FOUND` or `%NOTFOUND` to tell which cursors have rows left to fetch. Likewise, you can use `%ROWCOUNT` to tell how many rows have been fetched so far.

If a cursor or cursor variable is not open, referencing it with `%FOUND`, `%NOTFOUND`, or `%ROWCOUNT` raises the predefined exception `INVALID_CURSOR`.

When a cursor or cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set one at a time.

If a `SELECT` `INTO` statement returns more than one row, PL/SQL raises the predefined exception `TOO_MANY_ROWS` and sets `%ROWCOUNT` to 1, not the actual number of rows that satisfy the query.

Before the first fetch, `%NOTFOUND` evaluates to `NULL`. If `FETCH` never executes successfully, the `EXIT WHEN` condition is never `TRUE` and the loop is never exited. To be safe, you might want to use the following `EXIT` statement instead:

`EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;`

You can use the cursor attributes in procedural statements, but not in SQL statements.

Examples

For examples, see the following:

Example 6-7, "Using SQL%FOUND"
Example 6-8, "Using SQL%ROWCOUNT"
Example 6-10, "Fetching With a Cursor"
Example 6-15, "Using %ISOPEN"

Related Topics

"Cursor Declaration"
"Cursor Variables"
"Managing Cursors in PL/SQL"